﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FisheryPlatform.DataAccess.DBUtility;
using System.Data.SqlClient;
using FisheryPlatform.Entity;
using System.Data;
using FisheryPlatform.DataAccess.Term;

namespace FisheryPlatform.DataAccess.Post
{
    public class PostTermRelationshipsDB
    {
        private const string PARAM_POST_ID = "@PostId";
        private const string PARAM_TERM_ID = "@TermId";
        private static readonly string ConnectionString = SQLHelper.CONNECTION_STRING;

        private static void FillEntityFrom(SqlDataReader reader, PostTermRelationshipsEntity obj)
        {
            #region
            if (reader != null && !reader.IsClosed)
            {
                obj.PostId = reader.IsDBNull(reader.GetOrdinal("PostId")) ? 0 : reader.GetInt64(reader.GetOrdinal("PostId"));
                obj.TermId = reader.IsDBNull(reader.GetOrdinal("TermId")) ? 0 : reader.GetInt64(reader.GetOrdinal("TermId"));
            }
            #endregion
        }

        private static SqlParameter[] GetTermsParams(PostTermRelationshipsEntity obj)
        {
            #region
            SqlParameter[] dbParams ={
	                 SQLHelper.MakeParam(PARAM_POST_ID, SqlDbType.BigInt, 0,obj.PostId),
					 SQLHelper.MakeParam(PARAM_TERM_ID, SqlDbType.BigInt,0,obj.TermId)
			};

            return dbParams;
            #endregion
        }

        public static bool Save(long postId, List<PostTermRelationshipsEntity> objList, SqlTransaction sqlTran)
        {
            #region
            try
            {
                if (DeleteByPostId(postId, sqlTran))
                {
                    foreach (var obj in objList)
                    {
                        SqlParameter[] dbParams = GetTermsParams(obj);

                        var affectNum = SQLHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure, "PostTermRelationships_InsertUpdate", dbParams);
                        if (affectNum <= 0)
                        {
                            return false;
                        }
                    }
                }
                else
                {
                    return false;
                }
                return true;
            }
            catch (Exception e)
            {
                Exception ex = new Exception("PostTermRelationships-->InsertOrUpdate-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static bool DeleteByPostId(long postId, SqlTransaction sqlTran)
        {
            #region
            try
            {
                SqlParameter[] dbParams = new SqlParameter[]{
                        SQLHelper.MakeParam(PARAM_POST_ID,SqlDbType.BigInt,0,postId)
                    };

                SQLHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure, "PostTermRelationships_DeleteByPostId", dbParams);
                return true;
            }
            catch (Exception e)
            {
                Exception ex = new Exception("PostTermRelationships-->DeleteByPostId-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static Dictionary<long, List<TermEntity>> GetListByPostIds(string postIds)
        {
            #region
            Dictionary<long, List<TermEntity>> resultList = new Dictionary<long, List<TermEntity>>();
            try
            {
                SqlParameter[] dbParams = new SqlParameter[]{
                        SQLHelper.MakeParam("@PostIds",SqlDbType.VarChar,0,postIds.TrimEnd(','))
                    };
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    conn.Open();
                    var reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "PostTermRelationships_GetByPostIdsReturnTermList", dbParams);
                    long postId = 0;
                    while (reader.Read())
                    {
                        postId = reader.IsDBNull(reader.GetOrdinal("PostId")) ? 0 : reader.GetInt64(reader.GetOrdinal("PostId"));
                        var term = new TermEntity();
                        TermsDB.FillEntityFrom(reader, term);

                        if (resultList.ContainsKey(postId))
                        {
                            resultList[postId].Add(term);
                        }
                        else
                        {
                            resultList.Add(postId, new List<TermEntity>());
                            resultList[postId].Add(term);
                        }
                    }
                    conn.Close();


                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("PostTermRelationships-->GetListByPostIds-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return resultList;
            #endregion
        }

    }
}
